import json
import math

def combinations(n, k):
    if k < 0 or k > n:
        return 0
    return math.factorial(n) // (math.factorial(k) * math.factorial(n - k))

def sum_lower_triangle(matrix):
    total = 0
    for i in range(len(matrix)):
        for j in range(i + 1):
            total += matrix[i][j]
    return total

def get_db_connect_degree_dict(data):
    db_connect_degree = {}
    for db in data:
        i=0
        # print(i)
        # print(db.get('table_names_original'))

        table_no = len(db.get('table_names_original', []))
        # print(table_no)
        column_names_list = db.get('columns_names_original')
        # print(len(column_names_list))
        # print(column_names_list)
        table_col_range = [0]*table_no
        # print(table_col_range)
        tmp = 0
        while i < len(column_names_list):
            # print(tmp)
            # print(column_names_list[i][0])
            if tmp>=table_no:
                break
            if i==0:
                i+=1
                continue
            k=0
            while i+k<len(column_names_list) and column_names_list[i+k][0]==tmp:
                # print(i)
                k+=1
            # print(i)
            i = i+k
            table_col_range[tmp] = k
            tmp += 1
        
        # print(table_col_range)

        
        
        foreign_keys_list = db.get('foreign_keys', [])
        # print(foreign_keys_list)
        for f in foreign_keys_list:
            col_total = 0
            f0_t_no = 0
            f1_t_no = 0
            for i in range(len(table_col_range)):
                col_total+=table_col_range[i]
                if f[0] <= col_total:
                    f0_t_no = i
                    f[0] = f0_t_no
                    break
            for i in range(len(table_col_range)):
                col_total+=table_col_range[i]
                if f[1] <= col_total:
                    f1_t_no = i
                    f[1] = f1_t_no
                    break

        # print(foreign_keys_list)
        connection = [[0 for i in range(table_no)] for i in range(table_no)]
        for f in foreign_keys_list:
            # print(f)
            connection[f[0]][f[1]] = 1
            connection[f[1]][f[0]] = 1

        result = sum_lower_triangle(connection)
        # print(connection)
        db_name = db.get('db_id')
        db_connect_degree[db_name] = len(db.get('foreign_keys', []))/(combinations(len(db.get('table_names_original', [])),2)+len(foreign_keys_list)-result)

    # break
    return db_connect_degree

# 读取JSON文件
with open("./政务数据/table_structure.json", 'r',encoding='utf-8') as file:
    data = json.load(file)
db_connect_degree = get_db_connect_degree_dict(data)

print(len(db_connect_degree))
print(db_connect_degree)

total = 0
count = 0
distribution = [0,0,0,0,0]
for d in db_connect_degree.values():
    if d>=0 and d<0.2:
        distribution[0]+=1
    if d>=0.2 and d<0.4:
        distribution[1]+=1
    if d>=0.4 and d<0.6:
        distribution[2]+=1
    if d>=0.6 and d<0.8:
        distribution[3]+=1
    if d>=0.8 and d<=1.0:
        distribution[4]+=1
    # print(d)
    total += d
    count+=1
avg_d = total/count
print(count)
print(avg_d)
print(distribution)
# 访问foreign_keys属性
# foreign_keys = data[0].get('foreign_keys', [])

# foreign_keys现在包含了JSON文件中的foreign_keys属性
# print(foreign_keys)